﻿#include "YNMysql.h"
#include "mysql.h"
#include <iostream>
using namespace std;

namespace YN {
	bool YNMysql::Init()
	{
		Close();
		cout << "YNMysql::Init()" << endl;
		//新创建一个MYSQL 对象
		mysql = mysql_init(0);
		if (!mysql)
		{
			cerr << "mysql_init failed!" << endl;
			return false;
		}
		return true;
	}

	//清理占用的所有资源
	void  YNMysql::Close()
	{
		FreeResult();

		if (mysql)
		{
			mysql_close(mysql);
			mysql = NULL;
		}
		cout << "YNMysql::Close()" << endl;
	}


	//数据库连接（不考虑线程安全） flag设置支持多条语句
	bool YNMysql::Connect(const char*host, const char*user, const char*pass, const char*db, unsigned short port, unsigned long flag)
	{
		if (!mysql && !Init())
		{
			cerr << "Mysql connect failed! msyql is not init!" << endl;
			return false;
		}
		if (!mysql_real_connect(mysql, host, user, pass, db, port, 0, flag))
		{
			cerr << "Mysql connect failed!" << mysql_error(mysql) << endl;
			return false;
		}
		cout << "mysql connect success!" << endl;
		return true;
	}


	bool YNMysql::Query(const char*sql, unsigned long sqllen)
	{
		if (!mysql)
		{
			cerr << "Query failed:mysql is NULL" << endl;
			return false;
		}
		if (!sql)
		{
			cerr << "sql is null" << endl;
			return false;
		}
		if (sqllen <= 0)
			sqllen = (unsigned long)strlen(sql);
		if (sqllen <= 0)
		{
			cerr << "Query sql is empty or wrong format!" << endl;
			return false;
		}

		int re = mysql_real_query(mysql, sql, sqllen);
		if (re != 0)
		{
			cerr << "mysql_real_query failed!" << mysql_error(mysql) << endl;
			return false;
		}
		return true;

	}

	//Mysql参数的设定
	bool YNMysql::Options(YN_OPT opt, const void *arg)
	{
		if (!mysql)
		{
			cerr << "Option failed:mysql is NULL" << endl;
			return false;
		}
		int re = mysql_options(mysql, (mysql_option)opt, arg);
		if (re != 0)
		{
			cerr << "mysql_options failed!" << mysql_error(mysql) << endl;
			return false;
		}
		return true;
	}

	//连接超时时间
	bool YNMysql::SetConnectTimeout(int sec)
	{
		return Options(YN_OPT_CONNECT_TIMEOUT, &sec);
	}

	//自动重连，默认不自动
	bool YNMysql::SetReconnect(bool isre)
	{
		return Options(YN_OPT_RECONNECT, &isre);
	}

	//返回全部结果
	bool YNMysql::StoreResult()
	{
		if (!mysql)
		{
			cerr << "StoreResult failed:mysql is NULL" << endl;
			return false;
		}
		FreeResult();
		result = mysql_store_result(mysql);
		if (!result)
		{
			cerr << "mysql_store_result failed!" << mysql_error(mysql) << endl;
			return false;
		}
		return true;
	}

	//开始接收结果，通过Fetch获取
	bool YNMysql::UseResult()
	{
		if (!mysql)
		{
			cerr << "UseResult failed:mysql is NULL" << endl;
			return false;
		}
		FreeResult();
		result = mysql_use_result(mysql);
		if (!result)
		{
			cerr << "mysql_use_result failed!" << mysql_error(mysql) << endl;
			return false;
		}
		return true;
	}

	//释放结果集占用的空间
	void YNMysql::FreeResult()
	{
		if (result)
		{
			mysql_free_result(result);
			result = NULL;
		}
	}

	//获取一行数据
	std::vector<YNData> YNMysql::FetchRow()
	{
		std::vector<YNData> ret;
		do
		{
			if (!result)
				break;
			MYSQL_ROW row = mysql_fetch_row(result);
			if (!row)
				break;

			//获取列数
			int num = mysql_num_fields(result);
			unsigned long *lens = mysql_fetch_lengths(result);
			for (int i = 0; i < num; ++i)
			{
				YNData data(row[i]);
				data.size = lens[i];
				//获取类型
				auto field = mysql_fetch_field_direct(result, i);
				data.type = (FIELD_TYPE)field->type;
				ret.push_back(data);
			}
		} while (0);
		return ret;
	}

	//生成insert sql语句
	std::string YNMysql::GetInsertSql(XDATA &kv, std::string table)
	{
		string sql = "";
		do
		{
			if (kv.empty() || table.empty())
				break;
			sql += "insert into ";
			sql += table;
			
			auto ptr = kv.begin();
			auto end = kv.end();
			string keys = "";
			string vals = "";
			for (; ptr != end; ++ptr)
			{
				keys += "`";
				keys += ptr->first;
				keys += "`,";

				vals += "'";
				vals += ptr->second.data;
				vals += "',";
			}
			// 去除结尾最后一个逗号
			keys[keys.size() - 1] = ' ';
			vals[vals.size() - 1] = ' ';

			sql += "(";
			sql += keys;
			sql += ") values(";
			sql += vals;
			sql += ");";
		} while (0);

		return sql;
	}
	
	//插入非二进制数据
	bool YNMysql::Insert(XDATA &kv, std::string table)
	{
		if (!mysql)
		{
			cerr << "Insert failed:mysql is NULL" << endl;
			return false;
		}
		string sql = GetInsertSql(kv, table);
		if (sql.empty())
			return false;
		if (!Query(sql.c_str()))
			return false;
		int num = mysql_affected_rows(mysql);
		if (num <= 0)
			return false;
		return true;
	}

	//插入二进制数据
	bool YNMysql::InsertBin(XDATA kv, std::string table)
	{
		string sql = "";
		MYSQL_STMT *stmt = NULL;
		do
		{
			if (kv.empty() || table.empty() || !mysql)
				break;
			sql = "insert into `";
			sql += table;
			sql += "` ";
			string keys = "";
			string vals = "";

			auto ptr = kv.begin();
			auto end = kv.end();
			//绑定字段
			MYSQL_BIND bind[120] = { 0 };
			int i = 0;
			for (; ptr != end; ++ptr)
			{
				//拼接字段名
				keys += "`";
				keys += ptr->first;
				keys += "`,";
				//拼接值
				vals += "?,";
				bind[i].buffer = (void*)ptr->second.data;
				bind[i].buffer_length = ptr->second.size;
				bind[i].buffer_type = (enum_field_types)ptr->second.type;
				++i;
			}
			//去除最后一个逗号
			keys[keys.size() - 1] = ' ';
			vals[vals.size() - 1] = ' ';
			//拼接sql
			sql += "(";
			sql += keys;
			sql += ") values(";
			sql += vals;
			sql += ");";
			//预处理sql语句
			stmt = mysql_stmt_init(mysql);
			if (!stmt)
			{
				cerr << "mysql_stmt_init failed! " << mysql_error(mysql) << endl;
				break;
			}
			if (mysql_stmt_prepare(stmt, sql.c_str(), sql.length()) != 0)
			{
				cerr << "mysql stmt prepare failed " << mysql_error(mysql) << endl;
				break;
			}
			if (mysql_stmt_bind_param(stmt, bind) != 0)
			{
				cerr << "mysql_stmt_bind_param failed! " << mysql_error(mysql) << endl;
				break;
			}
			if (mysql_stmt_execute(stmt) != 0)
			{
				cerr << "mysql_stmt_execute failed! " << mysql_error(mysql) << endl;
				break;
			}
			mysql_stmt_close(stmt);
			return true;
		} while (0);
		if (stmt)
			mysql_stmt_close(stmt);
		return false;
	}

	//获取更新数据的sql语句 where语句中，用户要包含where
	string YNMysql::GetUpdateSql(XDATA &kv, std::string table, std::string where)
	{
		string sql = "";
		do
		{
			if (kv.empty() || table.empty()) break;
			sql += "update `";
			sql += table;
			sql += "` set ";
			auto ptr = kv.begin();;
			auto end = kv.end();
			//绑定字段
			MYSQL_BIND bind[120] = { 0 };
			int i = 0;
			for (; ptr != end; ++ptr)
			{
				sql += "`";
				sql += ptr->first;
				sql += "`='";
				sql += ptr->second.data;
				sql += "',";

				bind[i].buffer = (void*)ptr->second.data;
				bind[i].buffer_length = ptr->second.size;
				bind[i].buffer_type = (enum_field_types)ptr->second.type;
				++i;
			}
			//去除多余的逗号
			sql[sql.size() - 1] = ' ';
			sql += where;
		} while (0);
		return sql;
	}

	//返回更新数量，失败返回-1
	int YNMysql::Update(XDATA &kv, std::string table, std::string where)
	{
		do
		{
			if (!mysql) break;
			string sql = GetUpdateSql(kv, table, where);
			if (sql.empty()) break;

			if (!Query(sql.c_str())) break;
			return mysql_affected_rows(mysql);
		} while (0);
		return -1;
	}

	int YNMysql::UpdateBin(XDATA &kv, std::string table, std::string where)
	{
		string sql = "";
		MYSQL_STMT *stmt = NULL;
		do
		{
			if (kv.empty() || table.empty()) break;
			sql += "update `";
			sql += table;
			sql += "` set ";
			auto ptr = kv.begin();;
			auto end = kv.end();
			//绑定字段
			MYSQL_BIND bind[120] = { 0 };
			int i = 0;
			for (; ptr != end; ++ptr)
			{
				sql += "`";
				sql += ptr->first;
				sql += "`=?,";
				bind[i].buffer = (void*)ptr->second.data;
				bind[i].buffer_length = ptr->second.size;
				bind[i].buffer_type = (enum_field_types)ptr->second.type;
				++i;
			}
			//去除多余的逗号
			sql[sql.size() - 1] = ' ';
			sql += where;
			//预处理sql语句
			stmt = mysql_stmt_init(mysql);
			if (!stmt)
			{
				cerr << "mysql_stmt_init failed! " << mysql_error(mysql) << endl;
				break;
			}
			if (mysql_stmt_prepare(stmt, sql.c_str(), sql.length()) != 0)
			{
				cerr << "mysql stmt prepare failed " << mysql_error(mysql) << endl;
				break;
			}
			if (mysql_stmt_bind_param(stmt, bind) != 0)
			{
				cerr << "mysql_stmt_bind_param failed! " << mysql_error(mysql) << endl;
				break;
			}
			if (mysql_stmt_execute(stmt) != 0)
			{
				cerr << "mysql_stmt_execute failed! " << mysql_error(mysql) << endl;
				break;
			}
			mysql_stmt_close(stmt);
			return mysql_stmt_affected_rows(stmt);
		} while (0);
		if (stmt)
			mysql_stmt_close(stmt);
		return -1;
	}

	bool YNMysql::StartTransaction()
	{
		return Query("set autocommit=0");
	}

	bool YNMysql::StopTransaction()
	{
		return Query("set autocommit=1");
	}

	bool YNMysql::Commit()
	{
		return Query("commit");

	}

	bool YNMysql::Rollback()
	{
		return Query("rollback");
	}

	//简易接口,返回select的数据结果，每次调用清理上一次的结果集
	XROWS YNMysql::GetResult(const char *sql)
	{
		FreeResult();
		XROWS rows;
		if (!Query(sql))
			return rows;
		if (!StoreResult())
			return rows;
		for (;;)
		{
			auto row = FetchRow();
			if (row.empty())break;
			rows.push_back(row);
		}
		return rows;
	}
}
